<?php

namespace common\db;

use Yii;
use yii\db\Exception;

class Db
{
    function __construct()
    {
    }

    function one($sql, $iscache = false)
    {
        try {
            if ($iscache) {
                $cacheKey = md5($sql);
                $cacheValue = \Cm::$cache->get($cacheKey);
                if ($cacheValue)
                    return $cacheValue;

                $row = Yii::$app->db->createCommand($sql)->queryOne();
                \Cm::$cache->set($cacheKey, $row, 60 * 60);
            } else
                $row = Yii::$app->db->createCommand($sql)->queryOne();

            return $row;
        } catch (Exception $e) {
            $this->log($e->getMessage());
        }
    }

    function all($sql, $iscache = false)
    {
        try {
            if ($iscache) {
                $cacheKey = md5($sql);
                $cacheValue = \Cm::$cache->get($cacheKey);
                if ($cacheValue)
                    return $cacheValue;

                $row = Yii::$app->db->createCommand($sql)->queryAll();
                \Cm::$cache->set($cacheKey, $row, 60 * 60);
            } else
                $row = Yii::$app->db->createCommand($sql)->queryAll();

            return $row;

        } catch (Exception $e) {
            $this->log($e->getMessage());
        }
    }

    public function insert($table, $data)
    {
        try {
            Yii::$app->db->createCommand()->insert($table, $data)->execute();
            return YII::$app->db->getLastInsertId();
        } catch (Exception $e) {
            $this->log($e->getMessage());
        }
    }

    public function update($table, $data, $where = '')
    {
        try {
            Yii::$app->db->createCommand()->update($table, $data, $where)->execute();
        } catch (Exception $e) {
            $this->log($e->getMessage());
        }
    }

    public function delete($table, $where)
    {
        try {
            Yii::$app->db->createCommand()->delete($table, $where)->execute();
        } catch (Exception $e) {
            $this->log($e->getMessage());
        }
    }

    public function count($sql)
    {
        try {
            $count = Yii::$app->db->createCommand($sql)
                ->queryScalar();
            return $count ? $count : 0;
        } catch (Exception $e) {
            $this->log($e->getMessage());
        }
    }

    public function paging($sql, $pageIndex, $pageCount)
    {
        try {

            $sql = str_replace(array("\r\n", "\r", "\n"), " ", $sql);

            //替换原SQL select 字段部分 为 count(*) as num
            $arr = explode(' ', $sql);
            $flag = true;
            $num = 0;
            $countSql = 'select count(*) as num ';
            foreach ($arr as $i => $a) {
                $a = str_replace(' ', '', $a);

                preg_match_all('/\(/i', $a, $mh1);
                $num += count($mh1[0]);

                preg_match_all('/\)/i', $a, $mh2);
                $num -= count($mh2[0]);

                $a = trim($a);
                if (strtolower($a) == 'from' && $num === 0 && $flag) {
                    $flag = false;
                }
                if (!$flag)
                    $countSql .= $a . ' ';
            }

            $result = ['count' => 0, 'data' => []];

            $count = Yii::$app->db->createCommand($countSql)
                ->queryScalar();
            $result['count'] = $count ? $count : 0;

            if ($pageCount) {
                $limit = ($pageIndex - 1) * $pageCount . ',' . $pageCount;
                $sql .= ' limit ' . $limit;
            }

            $data = Yii::$app->db->createCommand($sql)
                ->queryAll();

            $result['data'] = $data;
            $result['size'] = (int)$pageCount;
            $result['pages'] = $pageCount ? ceil($result['count'] / $pageCount) : $result['count'];
            $result['index'] = (int)$pageIndex;

            return $result;

        } catch (Exception $e) {
            $this->log($e->getMessage());
        }
    }

    private function log($msg)
    {
        \Cm::$app->log->common("sql", $msg);
    }
}
